using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public CtacortipoVO getCtacortipo(int? idCtaCorTipo, string ctaCorTipo)
		{
			CtacortipoVO ctacortipoVO = null;
			List<CtacortipoVO> listCtacortipoVOs = getCtacortipos(idCtaCorTipo, ctaCorTipo, null);
			if (listCtacortipoVOs != null && listCtacortipoVOs.Count > 0)
			{
				ctacortipoVO = listCtacortipoVOs[0];
			}
			return ctacortipoVO;
		}

		public CtacortipoVO getCtacortipo(int? idCtaCorTipo)
		{
			return getCtacortipo(idCtaCorTipo, null);
		}

		public List<CtacortipoVO> getCtacortipos()
		{
			return getCtacortipos(null, null, null);
		}

		public List<CtacortipoVO> getCtacortipos(int? idCtaCorTipo)
		{
			return getCtacortipos(idCtaCorTipo, null, null);
		}

		public List<CtacortipoVO> getCtacortipos(int? idCtaCorTipo, string ctaCorTipo, int? idFilial)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT cct.idCtaCorTipo as cct_idCtaCorTipo, cct.ctaCorTipo as cct_ctaCorTipo, cct.idFilial as cct_idFilial, cct.cadastro as cct_cadastro, cct.cadastradoPor as cct_cadastradoPor, cct.edicao as cct_edicao, cct.editadoPor as cct_editadoPor  ");
			query.AppendLine("FROM ctacortipo as cct ");
			if (idCtaCorTipo != null)
			{
				query.AppendLine(where + "cct.idCtaCorTipo=@idCtaCorTipo ");
				where = "AND ";
			}
			if (ctaCorTipo != null)
			{
				query.AppendLine(where + "cct.ctaCorTipo LIKE @ctaCorTipo ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "cct.idFilial=@idFilial ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY cct.ctaCorTipo ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idCtaCorTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", idCtaCorTipo));
			if (ctaCorTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@ctaCorTipo", "%"+ctaCorTipo+"%"));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<CtacortipoVO> listCtacortipo = new List<CtacortipoVO>();

				while (reader.Read())
				{
					CtacortipoVO ctacortipoVO = CtacortipoVO.CreateVO();
					ctacortipoVO.idCtaCorTipo = reader.GetInt32(reader.GetOrdinal("cct_idCtaCorTipo"));
					ctacortipoVO.ctaCorTipo = reader.GetString(reader.GetOrdinal("cct_ctaCorTipo"));
					ctacortipoVO.idFilial = reader.GetInt32(reader.GetOrdinal("cct_idFilial"));
					ctacortipoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("cct_cadastro"));
					ctacortipoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("cct_cadastradoPor"));
					ctacortipoVO.edicao = reader.GetDateTime(reader.GetOrdinal("cct_edicao"));
					ctacortipoVO.editadoPor = reader.GetString(reader.GetOrdinal("cct_editadoPor"));


					listCtacortipo.Add(ctacortipoVO);
				}
				reader.Close();
				return listCtacortipo;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setCtacortipo(CtacortipoVO ctacortipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE ctacortipo ");
			query.AppendLine("SET ctaCorTipo=@ctaCorTipo, idFilial=@idFilial,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idCtaCorTipo=@idCtaCorTipo ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", ctacortipoVO.idCtaCorTipo));
			cmd.Parameters.Add(getParameter(cmd, "@ctaCorTipo", ctacortipoVO.ctaCorTipo));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ctacortipoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ctacortipoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ctacortipoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ctacortipoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ctacortipoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newCtacortipo(CtacortipoVO ctacortipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO ctacortipo ");
			query.AppendLine("  ( ctaCorTipo, idFilial, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @ctaCorTipo, @idFilial, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@ctaCorTipo", ctacortipoVO.ctaCorTipo));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ctacortipoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ctacortipoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ctacortipoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ctacortipoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ctacortipoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('ctacortipo') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delCtacortipo(CtacortipoVO ctacortipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM ctacortipo ");
			query.AppendLine("WHERE idCtaCorTipo=@idCtaCorTipo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", ctacortipoVO.idCtaCorTipo));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
	}
}
